package easybooking.db;

import easybooking.data.Flight;
import easybooking.data.Plane;
import easybooking.data.Seat;
import easybooking.data.SeatImpl;
import java.rmi.RemoteException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Seat</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.66FF5ABB-030D-20BB-F725-1C4E46974848]
// </editor-fold> 
public class SeatMapper extends Mapper {

    /**
     * The class <code>SeatMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see seatMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.989D0B2A-E73B-98FB-5557-9A74DCD6FEF8]
    // </editor-fold> 
    private static SeatMapper seatMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected SeatMapper() {
    }

    /**
     * The static method can be called with
     * <code>SeatMapper.seatMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>SeatMapper</code> exists.

     * <b>Conclusion:</b> The <code>SeatMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>SeatMapper</code> object.
     * @see seatMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.762AE915-1CE5-98C7-D51B-AE1210A010F6]
    // </editor-fold> 
    public static SeatMapper seatMapper() {
        if (seatMapper == null) {
            seatMapper = new SeatMapper();
        }
        return seatMapper;
    }

    /**
     * Searching a seat with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Seat object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.A1779EE8-C09F-4D62-2AB7-56E155FC6D0A]
    // </editor-fold> 
    public Seat findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT seatId, row, seat, planeId, isActive FROM Seat " +
                    "WHERE seatId=" + id);
            if (rs.next()) {
                try {
                    Seat s = new SeatImpl();
                    s.setSeatId(rs.getInt("seatId"));
                    s.setRow(rs.getInt("row"));
                    s.setSeat(rs.getString("seat").charAt(0));
                    s.setPlaneId(rs.getInt("planeId"));
                    s.setIsActive(rs.getBoolean("isActive"));
                    return s;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active seats from the database.
     *
     * @return A vector of seat objects, which includes all active seats.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.A9B8B217-3FB1-1836-169F-6C7088532E9F]
    // </editor-fold> 
    public Vector<Seat> findAll() {
        Connection con = DBConnection.connection();
        Vector<Seat> result = new Vector<Seat>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT seatId, row, seat, planeId, isActive FROM Seat " +
                    "WHERE isActive = true " +
                    "ORDER BY seatId ASC");
            while (rs.next()) {
                try {
                    Seat s = new SeatImpl();
                    s.setSeatId(rs.getInt("seatId"));
                    s.setRow(rs.getInt("row"));
                    s.setSeat(rs.getString("seat").charAt(0));
                    s.setPlaneId(rs.getInt("planeId"));
                    s.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(s);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Seat</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param s the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.C9C52376-F486-B21E-8D39-F6E0BDB07B06]
    // </editor-fold> 
    public Seat insert(Seat s) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(seatId) AS maxSeatId " +
                    "FROM Seat");
            if (rs.next()) {
                try {
                    s.setSeatId(rs.getInt("maxSeatId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Seat (seatId, row, seat, planeId, isActive) " +
                            "VALUES (" +
                            s.getSeatId() + ",'" +
                            s.getRow() + "','" +
                            s.getSeat() + "','" +
                            s.getPlaneId() + "','" +
                            booleanToInt(s.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return s;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param s the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.9CF659C4-3F9F-F046-330A-0ED3E65F9B5B]
    // </editor-fold> 
    public Seat update(Seat s) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Seat " +
                    "SET row=\"" + s.getRow() + "\", " +
                    "seat=\"" + s.getSeat() + "\", " +
                    "planeId=\"" + s.getPlaneId() + "\", " +
                    "isActive=\"" + booleanToInt(s.getIsActive()) + "\" " +
                    "WHERE seatId=" + s.getSeatId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        return s;
    }

    /**
     * Selecting all active seats from the database by a certain plane,
     * which is given through a parameter.
     *
     * @param planeId the id of a plane which should be searched for
     * @return A vector of seat objects, which includes all active seats
     * by a certain plane.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.6E2E98F3-7B6A-B598-1FCB-85D46FC92895]
    // </editor-fold> 
    public Vector<Seat> findByPlane(int planeId) {
        Connection con = DBConnection.connection();
        Vector<Seat> result = new Vector<Seat>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT seatId, row, seat, planeId, isActive FROM Seat " +
                    "WHERE planeId=" + planeId +
                    " AND isActive = true " +
                    "ORDER BY seatId ASC");
            while (rs.next()) {
                try {
                    Seat s = new SeatImpl();
                    s.setSeatId(rs.getInt("seatId"));
                    s.setRow(rs.getInt("row"));
                    s.setSeat(rs.getString("seat").charAt(0));
                    s.setPlaneId(rs.getInt("planeId"));
                    s.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(s);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active seats from the database by a certain plane,
     * which is given through a parameter.
     *
     * @param p the object of a plane which should be searched for
     * @return A vector of seat objects, which includes all active seats
     * by a certain plane. In case of an exception, an empty vector will
     * be returned.
     * @see findByPlane(int planeId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.87B2AABA-2E34-4E62-4CB2-FF901AC0A12B]
    // </editor-fold> 
    public Vector<Seat> findByPlane(Plane p) {
        try {
            return SeatMapper.seatMapper().findByPlane(p.getPlaneId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all booked seats from the database by a certain flight,
     * which is given through a parameter.
     *
     * @param flightId the id of a flight which should be searched for
     * @return A vector of seat objects, which includes all booked seats
     * by a certain flight.
     * In case of an exception, an empty vector will be returned.
     */
    public Vector<Seat> findBookedByFlight(int flightId) {
        Connection con = DBConnection.connection();
        Vector<Seat> result = new Vector<Seat>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT s.seatId, s.row, s.seat, s.planeId, s.isActive FROM Seat s " +
                    "LEFT JOIN Booking b ON b.seatId = s.seatId " +
                    "WHERE b.flightId=" + flightId + " AND b.isActive = true " +
                    "ORDER BY s.seatId ASC");
            while (rs.next()) {
                try {
                    Seat s = new SeatImpl();
                    s.setSeatId(rs.getInt("seatId"));
                    s.setRow(rs.getInt("row"));
                    s.setSeat(rs.getString("seat").charAt(0));
                    s.setPlaneId(rs.getInt("planeId"));
                    s.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(s);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return result;
    }

    /**
     * Selecting all booked seats from the database by a certain flight,
     * which is given through a parameter.
     *
     * @param f the object of a flight which should be searched for
     * @return A vector of seat objects, which includes all booked seats
     * by a certain flight. In case of an exception, an empty vector will
     * be returned.
     * @see findBookedByFlight(int flightId)
     */
    public Vector<Seat> findBookedByFlight(Flight f) {
        try {
            return SeatMapper.seatMapper().findBookedByFlight(f.getFlightId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Searching an active seat from the database by a certain row, a certain
     * seat and a certain plane, which are given through parameters.
     *
     * @param row the number of a row which should be searched for
     * @param seat the char of a seat which should be searched for
     * @param planeId the id of a plane which should be searched for
     * @return Seat object, which corresponds the given row, seat and plane,
     * <code>null</code> for a non existing database tupel.
     */
    public Seat findByRowSeatPlane(int row, char seat, int planeId) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT seatId, row, seat, planeId, isActive FROM Seat " +
                    "WHERE planeId=" + planeId +
                    " AND row=" + row +
                    " AND seat='" + seat + "' " +
                    "AND isActive = true " +
                    "ORDER BY seatId ASC");
            if (rs.next()) {
                try {
                    Seat s = new SeatImpl();
                    s.setSeatId(rs.getInt("seatId"));
                    s.setRow(rs.getInt("row"));
                    s.setSeat(rs.getString("seat").charAt(0));
                    s.setPlaneId(rs.getInt("planeId"));
                    s.setIsActive(rs.getBoolean("isActive"));
                    return s;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Searching an active seat from the database by a certain row, a certain
     * seat and a certain plane, which are given through parameters.
     *
     * @param row the number of a row which should be searched for
     * @param seat the char of a seat which should be searched for
     * @param p the object of a plane which should be searched for
     * @return Seat object, which corresponds the given row, seat and plane,
     * <code>null</code> for a non existing database tupel.
     * @see findByRowSeatPlane(int row, char seat, int planeId)
     */
    public Seat findByRowSeatPlane(int row, char seat, Plane p) {
        try {
            return SeatMapper.seatMapper().findByRowSeatPlane(row, seat, p.getPlaneId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }
}
